﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace DAL
{
    public class OmPurchaseOrderDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public OmPurchaseOrderDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 全部进货订单信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<OmPurchaseOrderDTO>> GetAllOmPurchaseOrderAsync()
        {
            string sql = @"SELECT 
                                  opo.up_order_id,
                                  opo.up_order_no,
                                  opo.order_status,
                                  opo.buyer_date,
                                  bs.supplier_name,
                                  bs.contact_person,
                                  bs.contact_phone,
                                  soz.org_name as buyer_org_name,
                                  u.users_name as buyer_by,
                                  COALESCE(SUM(opod.order_quantity), 0) as all_order_quantity,
                                  COALESCE(AVG(opod.purchase_unit_price), 0) as purchase_unit_price_count,
                                  COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY opod.up_order_id), '') AS all_goods_name,
                                  u2.users_name as create_by,
                                  soz2.org_name as create_org_name,
                                  u2.create_time,
                                  u3.users_name as auditor_by,
                                  opo.auditor_time
                                  FROM om_purchase_order opo
                                        LEFT JOIN om_purchase_order_detail opod ON opo.up_order_id = opod.up_order_id
                                        LEFT JOIN bi_product bp ON opod.goods_code = bp.goods_code
                                        JOIN sys_users u ON opo.buyer_by = u.users_id 
                                        JOIN sys_organization soz ON u.org_id = soz.org_id 
                                        JOIN bi_supplier bs ON opo.supplier_id = bs.supplier_id
                                        JOIN sys_users u2 ON opo.create_by = u2.users_id
                                        JOIN sys_organization soz2 ON u2.org_id = soz2.org_id
                                        left join sys_users u3 on opo.auditor_by = u3.users_id
                                  WHERE opo.is_delete = 0
                                  GROUP BY 
                                            opo.up_order_id,
                                            opo.up_order_no,
                                            opo.order_status,
                                            opo.buyer_date,
                                            bs.supplier_name,
                                            bs.contact_person,
                                            bs.contact_phone,
                                            soz.org_name,
                                            u.users_name,
                                            u2.users_name,
                                            soz2.org_name,
                                            u2.create_time,
                                            u3.users_name,
                                            auditor_by,
                                            opo.auditor_time,
                                            opo.update_time
                                order by opo.update_time desc";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<OmPurchaseOrderDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 查询全部部门信息，用于下拉选择框
        /// </summary>
        /// <returns></returns>
        public async Task<List<SysOrganizationDTO>> GetOrgInfoForSelectTable()
        {
            string sql = "select org_id,org_code,org_name,parent_org_id,org_level from sys_organization";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<SysOrganizationDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 获取全部用户信息，用于下拉框
        /// </summary>
        /// <returns></returns>
        public async Task<List<UsersDTO>> GetUsersInfoForSelectTable()
        {
            string sql = "select users_id,users_name,users_true_name,org_id from sys_users";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<UsersDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 获取全部供应商信息，用于下拉框
        /// </summary>
        /// <returns></returns>
        public async Task<List<BiSupplierDTO>> GetAllSupplierInfoForSelectTable()
        {
            string sql = "select supplier_id,supplier_name,contact_person,contact_phone from bi_supplier where is_delete=0";
            using( SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<BiSupplierDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 获取全部货品信息用于添加入库订单
        /// </summary>
        /// <returns></returns>
        public async Task<List<BiProductDTO>> GetAllGoodsInfoForAddGoods()
        {
            string sql = @"SELECT 
                                bp.goods_id,
                                bp.goods_code,
                                bp.goods_type_id,
                                bp.goods_name,
                                bpc.goods_type_name,  -- 注意：原SQL中字段名可能应为 bpc.goods_type_name（来自 bi_product_category）
                                bp.specification,
                                bp.purchase_reference_price,
                                SUM(wi.available_inventory_qty) AS total_available_inventory_qty  -- 求和并取别名
                            FROM 
                                bi_product bp
                            LEFT JOIN 
                                wh_inventory wi ON bp.goods_id = wi.goods_id 
                            INNER JOIN 
                                bi_product_category bpc ON bp.goods_type_id = bpc.goods_type_id
                            WHERE 
                                bp.is_delete = 0 
                                AND bpc.is_delete = 0
                            GROUP BY  -- 按货品唯一标识及其他非聚合字段分组
                                bp.goods_id,
                                bp.goods_code,
                                bp.goods_type_id,
                                bp.goods_name,
                                bpc.goods_type_name,
                                bp.specification,
                                bp.purchase_reference_price;";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<BiProductDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 新增进货订单
        /// </summary>
        /// <returns></returns>
        public async Task<int> AddOmpurchaseOrderInfo(
                                                        OmPurchaseOrderForAdd opof,
                                                        List<OmPurchaseOrderDetailForAdd> opodfList,  // 允许为空列表
                                                        List<SysAttachmentDTO> sadtoList)
        {
            // 1. 基础校验（仅校验主表，明细允许为空）
            if (opof == null)
                throw new ArgumentNullException(nameof(opof), "订单主表信息不能为空");

            // 2. SQL语句
            string orderSql = @"
                                INSERT INTO om_purchase_order
                                (up_return_id, up_order_no, supplier_id, order_status,
                                  buyer_by,buyer_date, is_delete, create_by, create_time,
                                 update_by, update_time, remarks)
                                OUTPUT INSERTED.up_order_id
                                VALUES
                                (@upReturnId, @upOrderNo, @supplierId, @orderStatus,
                                  @buyerBy,@buyerDate, @isDelete, @createBy, @createTime,
                                 @updateBy, @updateTime, @remarks);";

                                    string detailSql = @"
                                INSERT INTO om_purchase_order_detail
                                (up_order_id, goods_code, goods_type_id, purchase_unit_price,
                                 order_quantity, received_quantity, returned_quantity, 
                                 is_delete, attachment_code, remarks)
                                VALUES
                                (@upOrderId, @goodsCode, @goodsTypeId, @purchaseUnitPrice,
                                 @orderQuantity, @receivedQuantity, @returnedQuantity, 
                                 @isDelete, @attachmentCode, @remarks);";

            // 3. 数据库连接与事务
            var efConnection = _dbContext.Database.GetDbConnection();
            await efConnection.OpenAsync();
            using (var transaction = await efConnection.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted))
            {
                try
                {
                    _dbContext.Database.UseTransaction(transaction);

                    #region 步骤1：插入订单主表（必执行，无论是否为草稿）
                    long newOrderId = await efConnection.QuerySingleAsync<long>(
                        orderSql,
                        new
                        {
                            upReturnId = 0,
                            upOrderNo = opof.UpOrderNo,
                            supplierId = opof.SupplierId,
                            orderStatus = opof.OrderStatus ?? "1",  // 草稿为"0"，正式为"1"
                            buyerBy = opof.BuyerBy,
                            buyerDate = DateTime.Now,
                            isDelete = "0",
                            createBy = opof.CreateBy,
                            createTime = DateTime.Now,
                            updateBy = opof.CreateBy,
                            updateTime = DateTime.Now,
                            remarks = string.IsNullOrWhiteSpace(opof.Remarks) ? "无" : opof.Remarks
                        },
                        transaction: transaction
                    );
                    #endregion

                    #region 步骤2：插入订单明细（仅当明细非空时执行）
                    if (opodfList != null && opodfList.Count > 0)
                    {
                        var detailParams = opodfList.Select(detail => new
                        {
                            upOrderId = newOrderId,
                            goodsCode = detail.GoodsCode,
                            goodsTypeId = detail.GoodsTypeId,
                            purchaseUnitPrice = detail.PurchaseUnitPrice,
                            orderQuantity = detail.OrderQuantity,
                            receivedQuantity = 0,
                            returnedQuantity = 0,
                            isDelete = "0",
                            attachmentCode = detail.AttachmentCode ?? string.Empty,
                            remarks = string.IsNullOrWhiteSpace(detail.Remarks) ? "无" : detail.Remarks
                        }).ToList();

                        int totalDetailRows = await efConnection.ExecuteAsync(detailSql, detailParams, transaction);
                        if (totalDetailRows != opodfList.Count)
                            throw new Exception($"明细插入异常，预期{opodfList.Count}条，实际{totalDetailRows}条");
                    }
                    #endregion

                    #region 步骤3：插入附件（仅当有附件时执行）
                    if (sadtoList != null && sadtoList.Count > 0)
                    {
                        var attachmentEntities = sadtoList.Select(dto => new SysAttachment
                        {
                            AttachmentCode = dto.AttachmentCode ?? Guid.NewGuid().ToString(),
                            AttachmentAddress = dto.AttachmentAddress ?? throw new ArgumentException("附件地址不能为空", nameof(dto.AttachmentAddress)),
                            CreateBy = dto.CreateBy,
                            CreateTime = dto.CreateTime == default ? DateTime.Now : dto.CreateTime,
                            UpdateBy = dto.UpdateBy,
                            UpdateTime = dto.UpdateTime == default ? DateTime.Now : dto.UpdateTime
                        }).ToList();

                        _dbContext.SysAttachment.AddRange(attachmentEntities);
                        int attachmentRows = await _dbContext.SaveChangesAsync();
                        if (attachmentRows != sadtoList.Count)
                            throw new Exception($"附件插入异常，预期{sadtoList.Count}条，实际{attachmentRows}条");
                    }
                    #endregion

                    // 提交事务
                    await transaction.CommitAsync();
                    return (int)newOrderId;  // 返回新订单ID
                }
                catch (Exception ex)
                {
                    await transaction.RollbackAsync();
                    throw new InvalidOperationException($"创建采购订单失败：{ex.Message}", ex);
                }
                finally
                {
                    if (efConnection.State == ConnectionState.Open)
                        await efConnection.CloseAsync();
                }
            }
        }

        /// <summary>
        /// 查出要修改的进货订单信息
        /// </summary>
        /// <param name="upOrderId"></param>
        /// <returns></returns>
        public async Task<List<OmPurchaseOrderForUpdate>> SelectOmPurchaseOrderByUpOrderId(long upOrderId)
        {
            string sql = @"  select opo.up_order_id,
                                         up_order_no,
                                         buyer_date,
                                         supplier_id,
                                         sus.users_id as buyer_by,
                                         opo.remarks as om_p_o_remarks,
                                         opod.goods_code,
                                         opod.goods_type_id,
                                         purchase_unit_price,
                                         order_quantity,
                                         opod.remarks as om_p_o_d_remarks,
                                         attachment_address
                                    from om_purchase_order opo
                                    left join om_purchase_order_detail opod on opo.up_order_id = opod.up_order_id
                                    LEFT JOIN sys_attachment sat 
                                        ON opod.attachment_code = sat.attachment_code
                                    INNER JOIN sys_users sus 
                                        ON opo.buyer_by = sus.users_id
                                    WHERE opo.up_order_id = @UpOrderId;";
            using (SqlConnection con = new SqlConnection(URL))
            {
                await con.OpenAsync(); // 异步打开连接
                var result = await con.QueryAsync<OmPurchaseOrderForUpdate>(
                    sql: sql,
                    param: new { UpOrderId = upOrderId }
                );
                return result?.ToList() ?? new List<OmPurchaseOrderForUpdate>();
            }
        }

        /// <summary>
        /// 多条件查询进货订单信息
        /// </summary>
        /// <param name="sopows"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<List<OmPurchaseOrderDTO>> SearchOmPurOrderWithSmthFunc(SearchOmPurOrderWithSmth sopows)
        {
            var sqlHead = @"
                            SELECT 
                                opo.up_order_id,
                                opo.up_order_no,
                                opo.order_status,
                                opo.buyer_date,
                                bs.supplier_name,
                                bs.contact_person,
                                bs.contact_phone,
                                soz.org_name as buyer_org_name,
                                u.users_name as buyer_by,
                                COALESCE(SUM(opod.order_quantity), 0) as all_order_quantity,
                                COALESCE(AVG(opod.purchase_unit_price), 0) as purchase_unit_price_count,
                                COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY opod.up_order_id), '') AS all_goods_name,
                                u2.users_name as create_by,
                                soz2.org_name as create_org_name,
                                u2.create_time,
                                opo.auditor_by,
                                opo.auditor_time
                            FROM om_purchase_order opo
                                LEFT JOIN om_purchase_order_detail opod ON opo.up_order_id = opod.up_order_id
                                LEFT JOIN bi_product bp ON opod.goods_code = bp.goods_code
                                JOIN sys_users u ON opo.buyer_by = u.users_id 
                                JOIN sys_organization soz ON u.org_id = soz.org_id 
                                JOIN bi_supplier bs ON opo.supplier_id = bs.supplier_id
                                JOIN sys_users u2 ON opo.create_by = u2.users_id
                                JOIN sys_organization soz2 ON u2.org_id = soz2.org_id
                            WHERE opo.is_delete = 0";

            var sqlTail = @"
                            GROUP BY 
                                opo.up_order_id,
                                opo.up_order_no,
                                opo.order_status,
                                opo.buyer_date,
                                bs.supplier_name,
                                bs.contact_person,
                                bs.contact_phone,
                                soz.org_name,
                                u.users_name,
                                u2.users_name,
                                soz2.org_name,
                                u2.create_time,
                                opo.auditor_by,
                                opo.auditor_time,
                                opo.update_time
                            order by opo.update_time desc";

            // 2. 构建动态条件和参数（使用Dapper的匿名对象或DynamicParameters）
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 订单号（模糊查询）
            if (!string.IsNullOrEmpty(sopows.UpOrderNo))
            {
                conditions.Add("opo.up_order_no LIKE @UpOrderNo");
                parameters.Add("@UpOrderNo", $"%{sopows.UpOrderNo}%", DbType.String);
            }

            // 订单状态（精确匹配）
            if (!string.IsNullOrEmpty(sopows.OrderStatus))
            {
                conditions.Add("opo.order_status = @OrderStatus");
                parameters.Add("@OrderStatus", sopows.OrderStatus, DbType.String);
            }

            //供应商ID（精确匹配）
            if (sopows.SupplierId > 0)
            {
                conditions.Add("opo.supplier_id = @SupplierId");
                parameters.Add("@SupplierId", sopows.SupplierId, DbType.Int64);
            }

            // 开始时间（>=）
            if (sopows.StartTime.HasValue)
            {
                conditions.Add("opo.buyer_date >= @StartTime");
                parameters.Add("@StartTime", sopows.StartTime.Value, DbType.DateTime);
            }

            // 结束时间（<=）
            if (sopows.EndTime.HasValue)
            {
                conditions.Add("opo.buyer_date <= @EndTime");
                parameters.Add("@EndTime", sopows.EndTime.Value, DbType.DateTime);
            }

            // 3. 拼接最终SQL（条件不为空时添加AND连接）
            var finalSql = sqlHead;
            if (conditions.Count > 0)
            {
                finalSql += " AND " + string.Join(" AND ", conditions);
            }
            finalSql += sqlTail;

            // 4. 执行参数化查询（带异常处理）
            using (var con = new SqlConnection(URL)) // 假设URL是配置的连接字符串
            {
                try
                {
                    await con.OpenAsync(); // 显式打开连接（Dapper会自动打开，但显式打开更可控）
                    var result = await con.QueryAsync<OmPurchaseOrderDTO>(finalSql, parameters);
                    return result.AsList();
                }
                catch (SqlException ex)
                {
                    // 记录SQL异常（如连接失败、语法错误等）
                    Console.WriteLine($"数据库查询异常：{ex.Message}，SQL：{finalSql}");
                    throw new Exception("查询进货订单失败，请联系管理员", ex); // 包装异常，避免暴露敏感信息
                }
                catch (Exception ex)
                {
                    // 处理其他异常
                    Console.WriteLine($"查询异常：{ex.Message}");
                    throw;
                }
            }
        }

        /// <summary>
        /// 查看进货订单详情信息
        /// </summary>
        /// <param name="upOrderId"></param>
        /// <returns></returns>
        public async Task<OmPurchaseOrderDTO> CheckOmPurOrderDetailInfo(long upOrderId)
        {
            string sql = @"SELECT 
                                  opo.up_order_id,
                                  opo.up_order_no,
                                  opo.order_status,
                                  opo.buyer_date,
                                  bs.supplier_name,
                                  bs.contact_person,
                                  bs.contact_phone,
                                  soz.org_name as buyer_org_name,
                                  u.users_name as buyer_by,
                                  COALESCE(SUM(opod.order_quantity), 0) as all_order_quantity,
                                  COALESCE(AVG(opod.purchase_unit_price), 0) as purchase_unit_price_count,
                                  COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY opod.up_order_id), '') AS all_goods_name,
                                  u2.users_name as create_by,
                                  soz2.org_name as create_org_name,
                                  u2.create_time,
                                  opo.auditor_by,
                                  opo.auditor_time,
                                  opo.remarks 
                                  FROM om_purchase_order opo
                                        LEFT JOIN om_purchase_order_detail opod ON opo.up_order_id = opod.up_order_id
                                        LEFT JOIN bi_product bp ON opod.goods_code = bp.goods_code
                                        JOIN sys_users u ON opo.buyer_by = u.users_id 
                                        JOIN sys_organization soz ON u.org_id = soz.org_id 
                                        JOIN bi_supplier bs ON opo.supplier_id = bs.supplier_id
                                        JOIN sys_users u2 ON opo.create_by = u2.users_id
                                        JOIN sys_organization soz2 ON u2.org_id = soz2.org_id
                                  WHERE opo.is_delete = 0 and opo.up_order_id=@UpOrderId
                                  GROUP BY 
                                            opo.up_order_id,
                                            opo.up_order_no,
                                            opo.order_status,
                                            opo.buyer_date,
                                            bs.supplier_name,
                                            bs.contact_person,
                                            bs.contact_phone,
                                            soz.org_name,
                                            u.users_name,
                                            u2.users_name,
                                            soz2.org_name,
                                            u2.create_time,
                                            opo.auditor_by,
                                            opo.auditor_time,
                                            opo.remarks;";
            using (SqlConnection con = new SqlConnection(URL)) // URL 是你的数据库连接字符串
            {
                await con.OpenAsync();
                return await con.QueryFirstOrDefaultAsync<OmPurchaseOrderDTO>(
                    sql: sql,param: new { UpOrderId = upOrderId } // 匿名对象参数，Dapper 自动映射
                );
            }
        }

        /// <summary>
        /// 非审核修改进货订单状态
        /// </summary>
        /// <param name="statu"></param>
        /// <param name="upOrderId"></param>
        /// <returns></returns>
        public async Task<int> ChangeOmPurOrderStatu(string statu, long upOrderId,long updateBy)
        {
            string sql = "update om_purchase_order set order_status = @OrderStatus,update_by = @UpdateBy,update_time = @UpdateTime where up_order_id = @UpOrderId";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return await con.ExecuteAsync(
                    sql: sql,
                    param: new
                    {
                        OrderStatus = statu,
                        UpdateBy = updateBy,
                        UpdateTime = DateTime.Now,
                        UpOrderId = upOrderId
                    }
                );
            }
        }

        /// <summary>
        /// 审核进货订单
        /// </summary>
        /// <param name="auditorBy"></param>
        /// <param name="auditorComment"></param>
        /// <param name="statu"></param>
        /// <returns></returns>
        public async Task<int> AuditOmPurOrder(long auditorBy, string auditorComment, string statu,long upOrderId)
        {
            string sql = @"update om_purchase_order set 
                                                        order_status = @OrderStatus,
                                                        auditor_by = @AuditorBy,
                                                        auditor_time = @AuditorTime,
                                                        auditor_comment = @AuditorComment,
                                                        update_by = @AuditorBy,
                                                        update_time = @AuditorTime
                                                        where up_order_id = @UpOrderId";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return await con.ExecuteAsync(
                    sql: sql,
                    param: new
                    {
                        OrderStatus = statu,
                        AuditorBy = auditorBy,
                        AuditorTime = DateTime.Now,
                        AuditorComment = auditorComment,
                        UpOrderId = upOrderId
                    }
                );
            }
        }

        /// <summary>
        /// 批量删除进货订单
        /// </summary>
        /// <param name="upOrderList"></param>
        /// <returns></returns>
        public async Task<int> DeleteOmPurOrder(List<long> upOrderList, long updateBy)
        {
            // 1. 核心参数校验（防止空值、无效值，前端校验可能被绕过）
            if (upOrderList == null || !upOrderList.Any())
            {
                throw new ArgumentException("订单ID列表不能为空", nameof(upOrderList));
            }
            // 过滤无效ID（排除<=0的异常值，前端可能未严格校验）
            var validOrderIds = upOrderList.Where(id => id > 0).ToList();
            if (!validOrderIds.Any())
            {
                throw new ArgumentException("订单ID必须为正整数", nameof(upOrderList));
            }
            // 校验修改人ID（前端可能未传或传无效值）
            if (updateBy <= 0)
            {
                throw new ArgumentException("修改人ID无效", nameof(updateBy));
            }

            using (SqlConnection con = new SqlConnection(URL))
            {
                await con.OpenAsync();
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        // 2. 业务规则校验（核心！防止删除不允许删除的订单，前端校验可能被绕过）
                        // 即使前端校验了状态，后端仍需再次校验，确保数据安全
                        const string checkSql = @"
                                                SELECT up_order_id 
                                                FROM om_purchase_order 
                                                WHERE up_order_id IN @UpOrderIds 
                                                AND is_delete = '0'  -- 未被删除
                                                AND order_status IN ('0', '1')";  // 仅允许删除：草稿(0)/待审核(1)

                        var deletableOrderIds = await con.QueryAsync<int>(
                            checkSql,
                            new { UpOrderIds = validOrderIds },
                            transaction
                        );

                        if (!deletableOrderIds.Any())
                        {
                            transaction.Commit();
                            return 0; // 无符合条件的订单，返回0（不抛异常，避免前端误判）
                        }

                        // 3. 逻辑删除子表（记录修改人+时间）
                        const string deleteDetailSql = @"
                                    UPDATE om_purchase_order_detail 
                                    SET is_delete = '1'
                                    WHERE up_order_id IN @UpOrderIds 
                                    AND is_delete = '0'";

                        await con.ExecuteAsync(
                            deleteDetailSql,
                            new { UpOrderIds = deletableOrderIds, UpdateBy = updateBy },
                            transaction
                        );

                        // 4. 逻辑删除主表（记录修改人+时间）
                        const string deleteMainSql = @"
                            UPDATE om_purchase_order 
                            SET is_delete = '1',
                            update_time = GETDATE(),
                            update_by = @UpdateBy 
                            WHERE up_order_id IN @UpOrderIds 
                            AND is_delete = '0'";

                        var mainAffected = await con.ExecuteAsync(
                            deleteMainSql,
                            new { UpOrderIds = deletableOrderIds, UpdateBy = updateBy },
                            transaction
                        );

                        transaction.Commit();
                        return mainAffected;
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException("删除失败，请稍后重试", ex);
                    }
                }
            }
        }
    }
}
